USE [ServerMonitor]
GO
CREATE VIEW [dbo].[CurrentDBSize]
AS
  SELECT A.[DBName]
      ,CAST([DataSize] * 8.0 / 1024 AS DECIMAL(10,2)) AS DataSizeMB
      ,CAST([DataUsage]/([DataSize] * 1.0) AS DECIMAL(10,2)) AS DataUsage
      ,(SELECT MIN([DiskFreeMB])
      FROM [ServerMonitor].[dbo].[DBFile]
      WHERE [DBUsageId] = A.Id AND [FileType] = 'DATA') AS DataDiskMinFreeMB
      ,CAST([LogSize] * 8.0 / 1024 AS DECIMAL(10,2)) AS LogSizeMB
      ,CAST([LogUsage]/([LogSize] * 1.0) AS DECIMAL(10,2)) AS LogUsage   
      ,(SELECT MIN([DiskFreeMB])
      FROM [ServerMonitor].[dbo].[DBFile]
      WHERE [DBUsageId] = A.Id AND [FileType] = 'LOG') AS LogDiskMinFreeMB      
      ,A.[RecordTime]
  FROM [ServerMonitor].[dbo].[DBUsage] AS A INNER JOIN
  (SELECT MAX(RecordTime) AS RecordTime,DBName
  FROM [ServerMonitor].[dbo].[DBUsage]
  GROUP BY DBName) AS B
  ON A.[RecordTime] = B.RecordTime AND A.[DBName] = B.DBName
  INNER JOIN master.dbo.sysdatabases AS C
  ON A.[DBName] = C.name
GO


